<!DOCTYPE html>
<html lang="en">
<head><meta name="generator" content="Hexo 3.9.0">
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="google-site-verification" content="xBT4GhYoi5qRD5tr338pgPM5OWHHIDR6mNg1a3euekI">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <meta name="description" content="David的博客 |  David&#39;s Blog">
    <meta name="keyword" content="程序员小冰 David&#39;s Blog">
    <link rel="shortcut icon" href="/blog/img/favicon.ico">

    <title>
        
        【SQL】【MySQL】Mysql基本常用命令手册 - David 的博客 | David&#39;s Blog
        
    </title>

    <!-- Custom CSS -->
    <link rel="stylesheet" href="/blog/css/aircloud.css">
    <link rel="stylesheet" href="/blog/css/gitment.css">
    <!--<link rel="stylesheet" href="https://imsun.github.io/gitment/style/default.css">-->
    <link href="//at.alicdn.com/t/font_620856_pl6z7sid89qkt9.css" rel="stylesheet" type="text/css">
    <!-- ga & ba script hoook -->
    <script></script>
</head>

<body>

<div class="site-nav-toggle" id="site-nav-toggle">
    <button>
        <span class="btn-bar"></span>
        <span class="btn-bar"></span>
        <span class="btn-bar"></span>
    </button>
</div>

<div class="index-about">
    <i> David Blog </i>
</div>

<div class="index-container">
    
    <div class="index-left">
        
<div class="nav" id="nav">
    <div class="avatar-name">
        <div class="avatar radius">
            <img src="/blog/img/avatar.jpg" />
        </div>
        <div class="name">
            <i>David</i>
        </div>
    </div>
    <div class="contents" id="nav-content">
        <ul>
            <li >
                <a href="/blog/">
                    <i class="iconfont icon-shouye1"></i>
                    <span>主页</span>
                </a>
            </li>
            <li >
                <a href="/blog/tags">
                    <i class="iconfont icon-biaoqian1"></i>
                    <span>标签</span>
                </a>
            </li>
            <li >
                <a href="/blog/archives">
                    <i class="iconfont icon-guidang2"></i>
                    <span>存档</span>
                </a>
            </li>
            <li >
                <a href="/blog/about/">
                    <i class="iconfont icon-guanyu2"></i>
                    <span>关于</span>
                </a>
            </li>
            
            <li>
                <a id="search">
                    <i class="iconfont icon-sousuo1"></i>
                    <span>搜索</span>
                </a>
            </li>
            
        </ul>
    </div>
    
        <div id="toc" class="toc-article">
    <ol class="toc"><li class="toc-item toc-level-2"><a class="toc-link" href="#数据定义语句-DDL"><span class="toc-text">数据定义语句(DDL)</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#数据库操作"><span class="toc-text">数据库操作</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#表操作"><span class="toc-text">表操作</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#数据操纵语句-DML"><span class="toc-text">数据操纵语句(DML)</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#插入记录"><span class="toc-text">插入记录</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#修改记录"><span class="toc-text">修改记录</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#删除记录"><span class="toc-text">删除记录</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#查询记录"><span class="toc-text">查询记录</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#数据控制语句-DCL"><span class="toc-text">数据控制语句(DCL)</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#其他"><span class="toc-text">其他</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#字符集相关"><span class="toc-text">字符集相关</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#修改时区"><span class="toc-text">修改时区</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#权限相关"><span class="toc-text">权限相关</span></a></li></ol></li></ol>
</div>
    
</div>


<div class="search-field" id="search-field">
    <div class="search-container">
        <div class="search-input">
            <span id="esc-search"> <i class="icon-fanhui iconfont"></i></span>
            <input id="search-input"/>
            <span id="begin-search">搜索</span>
        </div>
        <div class="search-result-container" id="search-result-container">

        </div>
    </div>
</div>

        <div class="index-about-mobile">
            <i> David Blog </i>
        </div>
    </div>
    
    <div class="index-middle">
        <!-- Main Content -->
        


<div class="post-container">
    <div class="post-title">
        【SQL】【MySQL】Mysql基本常用命令手册
    </div>

    <div class="post-meta">
        <span class="attr">发布于：<span>2016-08-08 11:11:11</span></span>
        
        <span class="attr">标签：/
        
        <a class="tag" href="/blog/tags/#SQL" title="SQL">SQL</a>
        <span>/</span>
        
        
        </span>
        <span class="attr">访问：<span id="busuanzi_value_page_pv"></span>
</span>
</span>
    </div>
    <div class="post-content ">
        <h2 id="数据定义语句-DDL"><a href="#数据定义语句-DDL" class="headerlink" title="数据定义语句(DDL)"></a>数据定义语句(DDL)</h2><h3 id="数据库操作"><a href="#数据库操作" class="headerlink" title="数据库操作"></a>数据库操作</h3><ul>
<li>登录数据库：mysql -uroot -proot</li>
<li>创建数据库：create database test</li>
<li>查看所有数据库：show databases</li>
<li>使用数据库：use test</li>
<li>查看所有数据表：show tables</li>
<li>删除数据库：drop database test</li>
</ul>
<h3 id="表操作"><a href="#表操作" class="headerlink" title="表操作"></a>表操作</h3><ul>
<li>创建表：create table emp(ename varchar(10),hiredate date,sal decimal(10,2),deptno int(2))  <pre><code>create table dept(deptno int(2),deptname varchar(10))</code></pre></li>
<li>查看表的定义：desc emp</li>
<li>查看表定义（详细）：show create table emp \G</li>
<li>删除表：drop table emp</li>
<li>修改表字段：alter table emp modify ename varchar(20)</li>
<li>添加表字段：alter table emp add column age int(3)</li>
<li>删除表字段：alter table emp drop column age</li>
<li>字段改名；alter table emp change age age1 int(4)</li>
<li>修改表名：alter table emp rename emp1</li>
</ul>
<h2 id="数据操纵语句-DML"><a href="#数据操纵语句-DML" class="headerlink" title="数据操纵语句(DML)"></a>数据操纵语句(DML)</h2><h3 id="插入记录"><a href="#插入记录" class="headerlink" title="插入记录"></a>插入记录</h3><ul>
<li>指定名称插入：insert into emp (ename,hiredate,sal,deptno) values (‘zhangsan’,’2018-01-01’,’2000’,1)</li>
<li>不指定名称插入：insert into emp values (‘lisi’,’2018-01-01’,’2000’,1)</li>
<li>批量插入数据：insert into dept values(1,’dept1’),(2,’dept2’)</li>
</ul>
<h3 id="修改记录"><a href="#修改记录" class="headerlink" title="修改记录"></a>修改记录</h3><p>update emp set sal=’4000’,deptno=2 where ename=’zhangsan’</p>
<h3 id="删除记录"><a href="#删除记录" class="headerlink" title="删除记录"></a>删除记录</h3><p>delete from emp where ename=’zhangsan’</p>
<h3 id="查询记录"><a href="#查询记录" class="headerlink" title="查询记录"></a>查询记录</h3><ul>
<li>查询所有记录：select * from emp</li>
<li>查询不重复的记录：select distinct deptno from emp</li>
<li>条件查询：select * from emp where deptno=1 and sal&lt;3000</li>
<li>排序和限制：select * from emp order by deptno desc limit 2</li>
<li>聚合(查询部门人数大于1的部门编号)：select deptno,count(1) from emp group by deptno having count(1) &gt; 1</li>
<li>连接查询：select * from emp e left join dept d on e.deptno=d.deptno</li>
<li>子查询：select * from emp where deptno in (select deptno from dept)</li>
<li>记录联合：select deptno from emp union select deptno from dept</li>
</ul>
<h2 id="数据控制语句-DCL"><a href="#数据控制语句-DCL" class="headerlink" title="数据控制语句(DCL)"></a>数据控制语句(DCL)</h2><ul>
<li>授予操作权限：grant select,insert on test.* to ‘test’@’localhost’ identified by ‘123’</li>
<li>收回操作权限：revoke insert on test.* from ‘test’@’localhost’</li>
</ul>
<h2 id="其他"><a href="#其他" class="headerlink" title="其他"></a>其他</h2><h3 id="字符集相关"><a href="#字符集相关" class="headerlink" title="字符集相关"></a>字符集相关</h3><ul>
<li>查看字符集：show variables like ‘character%’</li>
<li>创建数据库时指定字符集：create database mall character set utf8</li>
</ul>
<h3 id="修改时区"><a href="#修改时区" class="headerlink" title="修改时区"></a>修改时区</h3><ul>
<li>修改mysql全局时区为北京时间，即我们所在的东8区：set global time_zone = ‘+8:00’;</li>
<li>修改当前会话时区：set time_zone = ‘+8:00’</li>
<li>立即生效：flush privileges</li>
</ul>
<h3 id="权限相关"><a href="#权限相关" class="headerlink" title="权限相关"></a>权限相关</h3><ul>
<li>授予所有数据库的所有权限：grant all privileges on <em>.</em> to z1@localhost identified by ‘123’</li>
<li>授予所有数据库的所有权限(包括grant)：grant all privileges on <em>.</em> to z1@localhost with grant option</li>
<li>授予SUPER PROCESS FILE权限：grant super,process,file on <em>.</em> to z3@localhost</li>
<li>只授予登录权限：grant usage on <em>.</em> to z4@localhost</li>
<li>查看账号权限：show grants for z1@localhost</li>
<li>修改自己的密码：set password = password(‘123’)</li>
<li>管理员修改他人密码：set password for ‘z1’@’localhost’ = password(‘123’)</li>
<li>删除账号：drop user z2@localhost</li>
</ul>

        
            <div class="donate-container">
    <div class="donate-button">
        <button id="donate-button">赞赏</button>
    </div>
    <div class="donate-img-container hide" id="donate-img-container">
        <img id="donate-img" src="" data-src="/blog/img/donate.jpg">
        <p> 感谢鼓励 </p>
    </div>
</div>
        
        <br />
        <div id="comment-container">
        </div>
        <div id="disqus_thread"></div>

        <div id="lv-container">
        </div>

    </div>
</div>

    </div>
</div>


<footer class="footer">
    <ul class="list-inline text-center">
        
        <li>
            <a target="_blank" href="https://twitter.com/qq986945193">
                            <span class="fa-stack fa-lg">
                                <i class="iconfont icon-twitter"></i>
                            </span>
            </a>
        </li>
        
        
        <li>
            <a target="_blank" href="https://www.zhihu.com/people/mcxiaobing">
                            <span class="fa-stack fa-lg">
                                 <i class="iconfont icon-zhihu"></i>
                            </span>
            </a>
        </li>
        

        
        <li>
            <a target="_blank" href="http://weibo.com/mcxiaobing">
                            <span class="fa-stack fa-lg">
                                  <i class="iconfont icon-weibo"></i>
                            </span>
            </a>
        </li>
        

        
        <li>
            <a target="_blank" href="https://www.facebook.com/qq986945193">
                            <span class="fa-stack fa-lg">
                                <i class="iconfont icon-facebook"></i>
                            </span>
            </a>
        </li>
        

        
        <li>
            <a target="_blank"  href="https://github.com/qq986945193">
                            <span class="fa-stack fa-lg">
                                <i class="iconfont icon-github"></i>
                            </span>
            </a>
        </li>
        

        

    </ul>
    
    <p>
        <span>/</span>
        
        <span><a href="https://segmentfault.com/u/mcxiaobing">思否</a></span>
        <span>/</span>
        
        <span><a href="https://juejin.im/user/57edcdb2a0bb9f0058ed8ed9">掘金</a></span>
        <span>/</span>
        
        <span><a href="https://www.jianshu.com/u/4e2015c5a972">简书</a></span>
        <span>/</span>
        
        <span><a href="https://gitee.com/mcxiaobing">码云</a></span>
        <span>/</span>
        
    </p>
    
    <p>
        <span id="busuanzi_container_site_pv">
            <span id="busuanzi_value_site_pv"></span>PV
        </span>
        <span id="busuanzi_container_site_uv">
            <span id="busuanzi_value_site_uv"></span>UV
        </span>
        Created By <a href="https://hexo.io/">Hexo</a>  Theme <a href="https://github.com/aircloud/hexo-theme-aircloud">AirCloud</a></p>
</footer>




</body>

<script>
    // We expose some of the variables needed by the front end
    window.hexo_search_path = "search.json"
    window.hexo_root = "/blog/"
    window.isPost = true
</script>
<script src="https://cdn.bootcss.com/jquery/3.3.1/jquery.min.js"></script>
<script src="/blog/js/index.js"></script>
<script async src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>




</html>
